﻿using Newtonsoft.Json;
using SAIM.Models.DAL;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

namespace SAIM.Models.BLL
{
    public class ListsBLL : IDisposable
    {
        /// <summary>
        /// Entitie Model.
        /// Utilizado para interactuar con la base de datos
        /// </summary>
        private eVirtualEntities db;

        public ListsBLL()
        {
            db = new eVirtualEntities();
        }

        /// <summary>
        /// Devuelve todas las listas de usuario existentes en la App Web
        /// </summary>
        /// <param name="userId">Id del usuario</param>
        /// <returns></returns>
        public Dictionary<string, string> GetLists(string userId)
        {
            Dictionary<string, string> userLists = new Dictionary<string, string>();
            // Obtenemos las listas del usuario
            var lists = (from list in db.Lists 
                              where (list.UsersLists.FirstOrDefault(ul => ul.UserId == userId) != null) 
                              select list);
            // Itermos las listas y construimos el diccionario a devolver
            foreach(ListsDAL list in lists)
            {
                // Agregamos el Id y el nombre de la lista al diccionario
                userLists.Add(list.Id, list.RealName);
            }
            return userLists;
        }

        /// <summary>
        /// Crea una nueva lista personalizada
        /// </summary>
        /// <param name="listName">Nombre de la lista</param>
        /// <param name="userId">ID de usuario creador</param>
        /// <returns>Devuelve verdadero si la lista fue creada</returns>
        public bool AddList(string listName, string userId)
        {
            string command; 
            bool success = false;
            // Inicializamos la lista a agregar
            ListsDAL list = new ListsDAL() { Id = Security.Hash(listName), RealName = listName, Structure = null, PrimaryUser = userId };
            // Inicializamos una instancia de la clase UsersListsDAL
            // la cual nos permitira relacionar al usuario con la lista
            UsersListsDAL userList = new UsersListsDAL() 
            { 
                UserId = userId, // ID del usuario
                ListId = list.Id, // ID de la lista
                DDLPermissions = true, // Permisos para modificar la estructura de la lista
                DMLPermissions = true // Permisos para modificar la informacion contenida dentro de la lista
            };

            // Comando a ejecutar para crear la tabla correspondiente a la lista
            command = "CREATE TABLE DataAppList." + list.Id + "(Id bigint NOT NULL PRIMARY KEY)";
            try
            {
                // Agregamos la lista
                db.Lists.Add(list);
                // Asociamos al usuario creador con la lista
                db.UsersLists.Add(userList);
                // Ejecutamos el comando de creacion de la tabla que representara la lista
                db.Database.ExecuteSqlCommand(command); // Creación de la tabla que almacenará la información de la lista
                db.SaveChanges();

                using(ListStructureBLL column = new ListStructureBLL())
                {
                    // Insertamos la columna Id dentro de la tabla correspondiente a la lista.
                    // Todas las listas requieren de esta columna para poder asociar archivos a ella
                    // y para poder agregar tuplas (En especial las de eleccion de datos)
                    bool addColumn = column.InsertColumn(new ColumnData()
                    {
                        ColumnName = "Id",
                        ListId = list.Id,
                        DataType = "Integer",
                        Required = true,
                        Unique = true,
                        Description = "Identificador único de cada tupla",
                        DefaultValue = null,
                        ChoiceType = null,
                        MinSize = null,
                        MaxSize = null,
                        DecimalPlaces = null,
                        Size = null,
                    }, false);

                    if(addColumn)
                        success = true;
                    else // Si la columna no fue creada eliminamos la lista
                    {
                        db.Lists.Remove(list);
                        db.UsersLists.Remove(userList); // Eliminamos la tupla correspondiente a la tabla
                        db.Database.ExecuteSqlCommand("DROP TABLE DataAppList." + list.Id); // Eliminacmos la tabla en la base de datos
                        db.SaveChanges();
                    }
                }
            }
            catch (Exception) 
            { 
                success = false; 
            }
            return success;
        }

        /// <summary>
        /// Elimina los archivos adjuntos de una lista
        /// </summary>
        /// <param name="listId">ID de la lista</param>
        /// <returns></returns>
        public bool RemoveListAttachments(string listId)
        {
            bool success = false;
            ListsDAL list = db.Lists.FirstOrDefault(l => l.Id == listId);

            if (list != null)
            {
                try
                {
                    // Encontramos todos los archivos adjuntos
                    var attachmentsList = (from e in db.ListsAttachments where e.ListId == listId select e);
                    using (AttachmentsBLL attachments = new AttachmentsBLL())
                    {
                        // Eliminamos cada archivo de forma individual
                        foreach (ListsAttachmentsDAL item in attachmentsList)
                            attachments.RemoveFile(listId, item.ColumnId, item.Attachments.Mask);
                    }
                    db.SaveChanges();
                    success = true;
                }
                catch (Exception) 
                { 
                    success = false; 
                }
            }
            return success;
        }

        /// <summary>
        /// Elimina una lista de la base de datos
        /// </summary>
        /// <param name="listId">ID de la lista a eliminar</param>
        /// <returns>Devuelve vardadero si la lista fue eliminada</returns>
        public bool RemoveList(string listId)
        {
            string command; 
            bool success = false;
            ListsDAL list = db.Lists.FirstOrDefault(l => l.Id == listId);

            if (list != null)
            {
                try
                {
                    // Busqueda de los usuarios que comparten la lista
                    List<UsersListsDAL> usersList = (from ul in db.UsersLists where (ul.ListId == listId) select ul).ToList();
                    // Busqueda de columnas 
                    List<ListStructureDAL> structureList = (from sl in db.ListStructure where (sl.ListId == listId) select sl).ToList();
                    // Eliminamos todos los usuarios de la lista (Uno a uno)
                    foreach (UsersListsDAL userDAL in usersList)
                    {
                        db.UsersLists.Remove(userDAL); 
                    }
                    // Eliminamos cada una de las columnas de la lista
                    using(ListStructureBLL columnBLL = new ListStructureBLL())
                    foreach(ListStructureDAL columnDAL in structureList)
                    {
                        // Si la columna es de tipo elección, eliminamos sus elecciones
                        if(columnDAL.ColumnType == "Choice")
                        {
                            if(!columnBLL.RemoveChoices(columnDAL.ColumnName, listId))
                                throw new Exception("Una o más opciones no pueden ser eliminadas");
                        }
                        db.ListStructure.Remove(columnDAL);
                    }
                    // Eliminamos la lista 
                    command = "DROP TABLE DataAppList." + listId;
                    db.Database.ExecuteSqlCommand(command); // Eliminamos la tabla en la base de datos
                    db.Lists.Remove(list);
                    db.SaveChanges();
                    success = true;
                }
                catch (Exception) 
                { 
                    success = false; 
                }
            }
            return success;
        }

        /// <summary>
        /// Devuelve una lista personalizada
        /// </summary>
        /// <param name="id">ID de la lista a buscar</param> 
        /// <param name="userId">ID del usuario a buscar en la lista</param>
        /// <returns></returns>
        public ListsViewModel GetList(string id, string userId)
        {
            ListsDAL list;
            ListsViewModel listModel = null;

            try
            {
                // Obtenemos la lista
                list = db.Lists.FirstOrDefault(l => l.Id == id);
                // Si la lista existe y el usuario se encuentra dentro de los usuarios de la lista 
                // procedemos a contruir el modelo a devolver
                if ((list != null) && (list.UsersLists.FirstOrDefault(ul => ul.UserId == userId) != null))
                {
                    /* Selección de todos los usuarios de la lista*/
                    List<UsersListsDAL> usersList = (from ul in db.UsersLists where (ul.ListId == id) select ul).ToList();
                    using (ListStructureBLL structure = new ListStructureBLL())
                    {
                        // Construccion del modelo
                        listModel = new ListsViewModel()
                        {
                            Id = list.Id, // ID de la lista
                            RealName = list.RealName, // Nombre a desplegar
                            PrimaryUser = list.PrimaryUser, // Usuario propietario de la lista
                            ListStructure = structure.GetListStructure(id) // Estructura de la lista
                        };
                    }
                    // Llenado de todos los usuarios que pueden interactuar en la lista
                    foreach (UsersListsDAL userDAL in usersList)
                    {
                        using (AspNetUsersBLL userBLL = new AspNetUsersBLL(userDAL.UserId))
                        {
                            // Asignación del usuario actual (Solicitante)
                            if(userDAL.UserId == userId)
                            {
                                listModel.ActualUser = new UsersLists()
                                {
                                    UserName = userBLL.getUserName(),
                                    UserUPN = userDAL.UserId,
                                    DDLPermissions = userDAL.DDLPermissions, // Indica si el usuario tiene permisos para alterar la estructura de la lista
                                    DMLPermissions = userDAL.DMLPermissions // Indica si el usuario tiene permisos para modificar la informacion almacenada en la lista
                                };
                            }
                            // Insercción de elementos a la lista de usuarios
                            listModel.Users.Add(new UsersLists()
                                {
                                    UserName = userBLL.getUserName(),
                                    UserUPN = userDAL.UserId,
                                    DDLPermissions = userDAL.DDLPermissions, // Indica si el usuario tiene permisos para alterar la estructura de la lista
                                    DMLPermissions = userDAL.DMLPermissions // Indica si el usuario tiene permisos para modificar la informacion almacenada en la lista
                                });
                        }
                    }
                    // Ordenamos los usuarios por orden alfabetico. El primer usuario siempre es el usuario propietario
                    listModel.Users.Sort((u1, u2) => (u2.UserUPN == listModel.PrimaryUser) ? 1 : u1.UserName.CompareTo(u2.UserName));
                }
            }
            catch (Exception) { /*(List Not Found) TODO: Add some code here to manage this exception*/ }
            return listModel;
        }

        /// <summary>
        /// Agrega un usuario a una lista específica
        /// Si el usuario ya existe en la lista actualiza sus datos
        /// </summary>
        /// <param name="listId">ID de la lista</param>
        /// <param name="user">Usuario a agregar</param>
        /// <returns>Devuelve verdadero si la relación Usuario-Lista fue creada</returns>
        public bool AddUpdateUser(string listId, UsersLists user)
        {
            bool success = false;

            try
            {
                // Busqueda del usuario en la lista 
                UsersListsDAL userDAL = db.UsersLists.FirstOrDefault(ul => ul.ListId == listId && ul.UserId == user.UserUPN);
                if (userDAL == null) // Agregamos al usuario
                {
                    // Creación de una nueva relación
                    userDAL = new UsersListsDAL()
                    {
                        ListId = listId,
                        UserId = user.UserUPN,
                        DDLPermissions = user.DDLPermissions, // Indica si el usuario tiene permisos para alterar la estructura de la lista
                        DMLPermissions = user.DMLPermissions // Indica si el usuario tiene permisos para modificar la informacion almacenada en la lista
                    };
                    db.UsersLists.Add(userDAL);
                }
                else // Actualizamos sus permisos
                {
                    // Modificación de la relación existente
                    userDAL.DDLPermissions = user.DDLPermissions;
                    userDAL.DMLPermissions = user.DMLPermissions;
                }
                db.SaveChanges();
            }
            catch (Exception) 
            { 
                success = false; 
            }
            return success;
        }

        /// <summary>
        /// Elimina la relacion entre un usuario y una lista 
        /// </summary>
        /// <param name="listId">ID de la lista</param>
        /// <param name="userId">Usuario a eliminar</param>
        /// <returns>Devuelve verdadero la relación eliminada</returns>
        public bool RemoveUser(string listId, string userId)
        {
            bool success = false;

            try
            {
                // Busqueda de la relacion entre el usuario y la lista
                UsersListsDAL userListDAL = db.UsersLists.FirstOrDefault(ul => (ul.ListId == listId) && (ul.UserId == userId) && (ul.Lists.PrimaryUser != userId));
                if (userListDAL != null)
                {
                    // Eliminamos de la relacion entre el usuario y la lista
                    db.UsersLists.Remove(userListDAL);
                    db.SaveChanges();
                    success = true;
                }
            }
            catch (Exception) 
            { 
                success = false; 
            }
            return success;
        }

        /// <summary>
        /// Devuelve una coleccion de columnas, las cuales componen una "lista personalizada especifica"
        /// </summary>
        /// <param name="listId">ID de la lista</param>
        /// <returns></returns>
        public List<ListStructure> GetListStructure(string listId)
        {
            List<ListStructureDAL> columnsDAL;
            List<ListStructure> columns;

            columns = new List<ListStructure>();
            try
            {
                // Obtenemos las columnas o estructura de la lista
                columnsDAL = (from col in db.ListStructure
                           where (col.ListId == listId && col.ColumnName != "Id")
                           orderby col.CreationDate ascending
                           select col).ToList();
            }
            catch (Exception)
            {
                columnsDAL = new List<ListStructureDAL>();
            }
            // Generamos una nueva clase con la informacion especifica de cada columna 
            // y la agregamos a la lista de columnas
            foreach(ListStructureDAL column in columnsDAL)
            {
                ListStructure itemList = new ListStructure()
                {
                    ChoiceType = column.ChoiceType,
                    ColumnDesc = column.ColumnDesc,
                    Label = column.ColumnName,
                    ColumnName = column.ColumnName.Replace(" ", ""),
                    ColumnType = column.ColumnType,
                    CreationDate = column.CreationDate,
                    DecimalPlaces = column.DecimalPlaces,
                    DefaultValue = column.DefaultValue,
                    IsRequired = column.IsRequired,
                    IsUnique = column.IsUnique,
                    ListId = column.ListId,
                    MaxValue = column.MaxValue,
                    MinValue = column.MinValue,
                    Size = column.Size
                };
                // Si la columna a agrega es de tipo eleccion, procedemos a buscar sus opciones
                if(itemList.ColumnType == "Choice")
                {
                    itemList.Choices = (from ch in db.Choices
                                        where (ch.ColumnName == itemList.ColumnName) && (ch.ListId == listId.Replace(" ", ""))
                                        select ch.ChoiceName).ToList();
                }
                columns.Add(itemList);
            }
            return columns;
        }

        /// <summary>
        /// Devuelve la informacion contenida dentro de una lista
        /// </summary>
        /// <param name="listId">ID de la lista</param>
        /// <param name="sort">Campo a utilizar para ordenar los datos</param>
        /// <param name="searchPhrase">Frase utilizada para filtrar los registros</param>
        /// <returns></returns>
        public List<Dictionary<string, object>> GetData(string listId, Dictionary<object, string> sort, string searchPhrase)
        {
            var cmd = db.Database.Connection.CreateCommand();
            List<string> columnsList;
            string sortKey;
            string sortValue;
            string whereClause;
            bool exists;

            exists = false;
            // Obtenemos todas las columnas de la lista
            columnsList = (from col in db.ListStructure
                           where (col.ListId == listId)
                           orderby col.CreationDate ascending
                           select col.ColumnName).ToList();
            // Obtenemos la columna mediante la cual vamos a ordenar y el tipo de ordenamiento
            sortKey = sort.Keys.First().ToString();
            sortValue = sort.Values.First().ToString();
            // Si existe una frase incializamos la clausula where
            if (searchPhrase != string.Empty)
            {
                whereClause = "WHERE";

            }
            else
            {
                whereClause = string.Empty;
            }
            // Construccion de la clausula where
            foreach (string item in columnsList)
            {
                // Si existe la columna mediante la cual se debe ordenar entonces se indica su existencia
                if (item.Replace(" ", "") == sortKey)
                {
                    exists = true;
                }
                if (searchPhrase != string.Empty)
                {
                    whereClause += string.Format(" [{0}] LIKE '%{1}%' OR", item.Replace(" ", ""), searchPhrase);
                }
            }
            if (searchPhrase != string.Empty)
            {
                // Eliminamos los ultimos 3 caracteres de la clausula WHERE construida " OR"
                whereClause = whereClause.Substring(0, whereClause.Length - 3);
            }
            // Si existe alguna columna mediante la cual ordenaremos construimos la sentencia utilizando dicha columna
            if (exists)
            {
                cmd.CommandText = string.Format("SELECT * FROM DataAppList.{0} {3} ORDER BY {1} {2}", listId, sortKey.Replace(" ", ""), sortValue, whereClause);
            }
            else // Por defecto ordenamos mediante el ID de la columna
            {
                cmd.CommandText = string.Format("SELECT * FROM DataAppList.{0} {1} ORDER BY Id ASC", listId, whereClause);
            }
            db.Database.Connection.Open();
            var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            var r = SerializeTable(reader, listId); // Seralizacion de la tabla 

            return r;
        }

        /// <summary>
        /// Devuelve la informacion de una tupla especifica
        /// </summary>
        /// <param name="listId">ID de la lista</param>
        /// <param name="rowId">ID de la tupla o renglon</param>
        /// <returns></returns>
        public Dictionary<string, object> GetRow(string listId, string rowId)
        {
            var cmd = db.Database.Connection.CreateCommand();
            cmd.CommandText = string.Format("SELECT * FROM DataAppList.{0} WHERE Id = {1} ORDER BY Id ASC", listId, rowId);
            // Abrimos la conexion con la base de datos y ejecutamos la consulta
            db.Database.Connection.Open();
            var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            // Generamos el diccionario a devolver
            var r = SerializeTable(reader, listId);

            return r.FirstOrDefault();
        }

        /// <summary>
        /// Serializa la informacion obtenida de una tabla mediante una variable de tipo DbDataReader
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="listId">ID de la lista</param>
        /// <returns></returns>
        public List<Dictionary<string, object>> SerializeTable(DbDataReader reader, string listId)
        {
            var results = new List<Dictionary<string, object>>();
            var cols = new List<string>();
            // Obtenemos los nombres de todas las columnas
            for (var i = 0; i < reader.FieldCount; i++)
            {
                cols.Add(reader.GetName(i));
            }
            // Agregamos la informacion contenida dentro de cada columna a un diccionario
            while (reader.Read())
            {
                results.Add(SerializeRow(cols, reader, listId));
            }
            return results;
        }

        /// <summary>
        /// Serializa la informacion de una tupla
        /// </summary>
        /// <param name="cols">Coleccion de columnas contenidas dentro de la tupla</param>
        /// <param name="reader"></param>
        /// <param name="listId">ID de la lista</param>
        /// <returns></returns>
        private Dictionary<string, object> SerializeRow(IEnumerable<string> cols, DbDataReader reader, string listId)
        {
            var result = new Dictionary<string, object>();
            // Obtenemos la estructura de la lista
            List<ListStructure> listStructure = GetListStructure(listId);
            // Iteramos las columnas y obtenemos la informacion que contienen
            foreach (var col in cols)
            {
                // Buscamos la columna actual en la estructura de la lista. Posteriormente devolvemos el valor
                // de la columna en base al tipo de columna indicado en la estructura obtenida
                ListStructure structure = listStructure.Find(a => a.ColumnName == col);
                if (structure != null)
                {
                    switch (structure.ColumnType)
                    {
                        case "Date":
                            try
                            {
                                DateTime date = ((DateTime)reader[col]);
                                // Formato YYYY/MM/DD
                                result.Add(col, date.Year.ToString() + '/' + date.Month.ToString() + '/' + date.Day.ToString());
                            }
                            catch (Exception)
                            {
                                result.Add(col, string.Empty);
                            }
                            break;
                        case "Time":
                            try
                            {
                                // Formato hh:mm:ss
                                result.Add(col, ((TimeSpan)reader[col]).ToString());
                            }
                            catch (Exception)
                            {
                                result.Add(col, string.Empty);
                            }
                            break;
                        case "DateTime":
                            try
                            {
                                DateTime date = ((DateTime)reader[col]);
                                // Formato YYYY/MM/DD hh:mm:ss
                                result.Add(col, date.Year.ToString() + '/' + date.Month.ToString() + '/' + date.Day.ToString() + ' ' + date.TimeOfDay.ToString());
                            }
                            catch (Exception)
                            {
                                result.Add(col, string.Empty);
                            }
                            break;
                        case "Decimal":
                            try
                            {
                                result.Add(col, ((decimal)reader[col]).ToString());
                            }
                            catch (Exception)
                            {
                                result.Add(col, string.Empty);
                            }
                            break;
                        default:
                            result.Add(col, reader[col].ToString());
                            break;
                    }
                }
                else
                {
                    result.Add(col, reader[col].ToString());
                }
            }
            return result;
        }

        public void Dispose()
        {
            db.Dispose();
        }
    }
}